/**
 * Class:   BeneficiaryManager
 * 
 * Methods:
 * 
 *      boolean insertBeneficiary( String beneficiary );
 *      
 *      ArrayList<String> searchBeneficiary( int activityID );
 *      
 *      public ArrayList<String> viewBeneficiary();
 *      
 *      void deleteBeneficiaryWithNoActivity();
 *      
 *      int getBeneficiaryID( String beneficiary );
 * 
 * Remarks:
 * 
 * 		This class can be touched by the controllers, but
 * 		only for one method.
 * 
 * @author ( Sharmaine Lim )
 * @version ( 2013-02-28 // YYYY-MM-DD )
 */

package managers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class BeneficiaryManager extends DatabaseManager {
    
    /**
     * Constructors
     */
    
    public BeneficiaryManager() {}
    
    
    
    /**
     *  Insert Beneficiary
     *  	- inserts an beneficiary into the beneficiary database
     *  
     *  Parameters
     *		- String beneficiary: the beneficiary to be added
     *	
     *	Returns
     *		- boolean true: beneficiary is successfully inserted
     * 		- boolean false: insertion failed
     */
    boolean insertBeneficiary( String beneficiary ) {
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
    		
    		connect();
    		
    		preparedStatement = getConnection()
    							.prepareStatement( "INSERT INTO `beneficiary`" +
    											   " (`beneficiaryName`)" +
    											   " VALUES (?)" );
    		
    		preparedStatement.setString( 1, beneficiary );
    		preparedStatement.executeUpdate();
    		
    		disconnect();
    		
    		if ( getBeneficiaryID( beneficiary ) != 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
	
	
	
	/**
	 *  Search Beneficiary
	 *  	- gets all beneficiaries in a certain activity
	 *  	  from the beneficiary database
	 *  
	 *  Parameters
	 *		- int activityID: the ID of the activity the beneficiaries
	 *						  are from 
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no beneficiaries found
	 * 		- ArrayList<String>.size() > 0: beneficiaries found
	 */
	ArrayList<String> searchBeneficiary( int activityID ) {
		
		/* Declaration of Local Variables */
		ArrayList<String> beneficiaryList = new ArrayList<String>();
		
		try {
    		/* Declaration of Block Variables */
			Statement statement;
			ResultSet resultSet;
			
			connect();
			
			statement = getConnection().createStatement();
			
			resultSet = statement
						.executeQuery( "SELECT * FROM `beneficiary` WHERE" +
									   " `beneficiaryID` IN" +
									   " (SELECT `beneficiaryID`" +
									   " FROM `v_activityBeneficiary` WHERE" +
									   " `activityID` = " + activityID + ")" );
			
			while ( resultSet.next() ) {
				beneficiaryList.add( resultSet.getString( "beneficiaryName" ) );
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return beneficiaryList;
	}
	
	
	
	/**
	 *  View Beneficiary
	 *  	- gets all beneficiaries from the beneficiary database
	 *  
	 *  Parameters
	 *		- n/a 
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no beneficiaries found
	 * 		- ArrayList<String>.size() > 0: beneficiaries found
	 */
	public ArrayList<String> viewBeneficiary() {
		
		/* Declaration of Local Variables */
		ArrayList<String> beneficiaryList = new ArrayList<String>();
		
		try {
    		/* Declaration of Block Variables */
			Statement statement;
			ResultSet resultSet;
			
			connect();
			
			statement = getConnection().createStatement();
			
			resultSet = statement
						.executeQuery( "SELECT * FROM `beneficiary`" );
			
			while ( resultSet.next() ) {
				beneficiaryList.add( resultSet.getString( "beneficiaryName" ) );
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return beneficiaryList;
	}
    
    
    
    /**
     *  Delete Beneficiary with No Activity
     *  	- deletes a beneficiary with no corresponding 
     *  	  activity in the v_activityBeneficiary database
     *  
     *  Parameters
     *		- n/a
     *	
     *	Returns
     *		- n/a
     */
    void deleteBeneficiaryWithNoActivity() {
    	try {
    		/* Declaration of Block Variables */
    		Statement statement;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		statement.executeUpdate( "DELETE FROM `beneficiary` WHERE" +
    								 " `beneficiaryID` NOT IN" +
    								 " (SELECT DISTINCT `beneficiaryID`" +
    								 " FROM `v_activityBeneficiary`) AND" +
    								 " `beneficiaryID` NOT IN" +
    								 " (SELECT DISTINCT `beneficiaryID`" +
    								 " FROM `c_activityBeneficiary`)" );
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
    	}
    }
    
    
    
    /**
     *  Get Beneficiary ID
     *  	- finds the ID of the beneficiary in the beneficiary database
     *  
     *  Parameters
     *		- String beneficiary: the beneficiary to be found
     *	
     *	Returns
     *		- non-zero int: beneficiary ID found
     * 		- int 0: beneficiary not found
     */
    int getBeneficiaryID( String beneficiary ) {
    	try {
    		/* Declaration of Block Variables */
    		int beneficiaryID = 0;
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		resultSet = statement
    					.executeQuery( "SELECT `beneficiaryID`" +
    								   " FROM `beneficiary`" +
    								   " WHERE `beneficiaryName` = \"" +
    								   beneficiary + "\"" );
    		
    		while ( resultSet.next() ) {
    			beneficiaryID = resultSet.getInt( "beneficiaryID" );
    		}
    		
    		resultSet.close();
    		disconnect();
    		return beneficiaryID;
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return 0;
    }
}
